<?php

class Student {
	
	function __construct() {}
	
	function getAllQuestionsInTest($tid) {
		global $db;
		$sql = "SELECT * FROM testquestions WHERE TestID=$tid";
		$result = $db->db_query($sql);
		if($result) {
			$rows = $db->db_fetchrowset($result);
			return $rows;
		} else return false;
	}
	
	function getAllQuestionsAnswerInTest($tid, $sid, $attempt) {
		global $db;
		$sql = "SELECT tq.*, ta.Attempt, ta.Mark as ta_Mark, ta.Answer, ta.Option1 as ta_Option1, ta.Option2 as ta_Option2, ta.Option3 as ta_Option3, 
				ta.Option4 as ta_Option4, ta.Option5 as ta_Option5, ta.Feedback as ta_Feedback
				FROM testquestions tq 
				LEFT JOIN testanswers ta ON tq.TestID=ta.TestID AND tq.QuestionNo=ta.QuestionNo 
				WHERE ta.TestID=$tid AND ta.StudentID=$sid AND ta.Attempt=$attempt";
		$result = $db->db_query($sql);
		if($result) {
			$rows = $db->db_fetchrowset($result);
			return $rows;
		} else return false;
	}
	
	function answerQuestionsAtFirst($testid, $questionid, $sid, $answer, $mark, $op1, $op2, $op3, $op4, $op5, $attempt) {
		global $db;
		$questionid = intval($questionid);
		$sql = "INSERT INTO testanswers(
			`TestID`,
			`QuestionNo`,	
			`StudentID`,
			`Answer`,
			`Mark`,
			`Feedback`,
			`Option1`,
			`Option2`,
			`Option3`,
			`Option4`,
			`Option5`,
			`Attempt`
		) VALUES(
			$testid,
			$questionid,
			$sid,
			'".$answer."',
			$mark,
			NULL,
			'".$op1."',
			'".$op2."',
			'".$op3."',
			'".$op4."',
			'".$op5."',
			$attempt
		)";
		//echo $sql;exit;
		$result = $db->db_query($sql);
		if($result) {
			return true;
		} else return false;
	}
	
	function answerQuestionsAtSecond($testid, $questionid, $sid, $answer, $mark, $op1, $op2, $op3, $op4, $op5, $attempt) {
		global $db;
		$questionid = intval($questionid);
		$sql = "UPDATE testanswers SET Answer='".$answer."', Mark=$mark, Option1='".$op1."', Option2='".$op2."', Option3='".$op3."', Option4='".$op4."', 
		Option5='".$op5."' 
		WHERE TestID=$testid AND QuestionNo=$questionid AND StudentID=$sid";
		$result = $db->db_query($sql);
		if($result) {
			return true;
		} else return false;
	}
	
	function checkAnswerExist($testid, $questionid, $sid) {
		global $db;
		$sql = "SELECT * FROM testanswers WHERE TestID=$testid AND QuestionNo=$questionid AND StudentID=$sid";
		$result = $db->db_query($sql);
		$exist = $db->db_numrows($result);
		if($exist == 1) {
			return true;
		} else return false;
	}
	
	function addTestAttempts($tid, $sid, $mark, $attempt) {
		global $db;
		$sql = "INSERT INTO testattempts(TestID, StudentID, OverallMark, Attempt) VALUES($tid, $sid, $mark, $attempt)";
		$result = $db->db_query($sql);
		if($result) {
			return true;
		} else return false;
	}
	
	function check_do_test($tid, $sid) {
		global $db;
		$sql = "SELECT * FROM testattempts WHERE TestID=$tid AND StudentID=$sid ORDER BY Attempt DESC LIMIT 1";
		$result = $db->db_query($sql);
		$exist = $db->db_numrows($result);
		if($result) {
			$row = $db->db_fetchrow($result);
			return $row;
		} else return false;
	}
	
	function do_check_answer_and_get_mark($tid, $qid, $where_clause) {
		global $db;
		$sql = "SELECT MaxMarks FROM testquestions WHERE TestID=$tid AND QuestionNo=$qid ";
		$nsql = $sql.$where_clause;
		//echo $nsql;exit;
		$result = $db->db_query($nsql);
		if($result) {
			$row = $db->db_fetchrow($result);
			return $row[0];
		} else return 0;
	}
	
	function showAllDoneTest($sid) {
		global $db;
		$sql = "SELECT * FROM (SELECT t.*, at.Attempt, at.StudentID, at.OverallMark, at.TestStatus FROM tests t
				JOIN testattempts at
				ON t.TestID=at.TestID
				WHERE at.StudentID=$sid AND t.UnitID IS NOT NULL ORDER BY at.Attempt DESC) as a GROUP BY a.TestID";
		//echo $sql;exit;
		$result = $db->db_query($sql);
		if($result) {
			$rows = $db->db_fetchrowset($resutl);
			return $rows;
		} else return false;
	}
	
	function showAllDoneTestsInModule($sid) {
		global $db;
		$sql = "SELECT * FROM (SELECT t.*, at.Attempt, at.StudentID, at.OverallMark, at.TestStatus FROM tests t
				JOIN testattempts at
				ON t.TestID=at.TestID 
				WHERE at.StudentID=$sid AND t.UnitID IS NULL ORDER BY at.Attempt DESC) as a GROUP BY a.TestID";
		$result = $db->db_query($sql);
		if($result) {
			$rows = $db->db_fetchrowset($resutl);
			return $rows;
		} else return false;
	}
	
	function sumMarks($tid, $sid, $attempt) {
		global $db;
		$sql = "SELECT SUM(Mark) FROM testanswers WHERE TestID=$tid AND StudentID=$sid AND Attempt=$attempt";
		$result = $db->db_query($sql);
		if($result) {
			$row = $db->db_fetchrow($result);
			return $row[0];
		} else return false;
	}
	
	function getAllMarkOfAssessment($tid, $sid) {
		global $db;
		$sql = "SELECT OverallMark FROM testattempts WHERE TestID=$tid AND StudentID=$sid";
		$result = $db->db_query($sql);
		if($result) {
			$rows = $db->db_fetchrowset($result);
			return $rows;
		} else return false;
	}
	
	function check_module_test($tid, $sid) {
		global $db;
		$sql = "SELECT at.* FROM testattempts at JOIN tests te ON at.TestID=te.TestID AND te.UnitID IS NULL 
		WHERE at.TestID=$tid AND at.StudentID=$sid ORDER BY at.Attempt DESC LIMIT 1";
		$result = $db->db_query($sql);
		$exists = $db->db_numrows($result);
		if($exists == 1) {
			return true;
		} else return false;
	}
	
	function check_test_attempt($tid, $sid, $attempt) {
		global $db;
		$sql = "SELECT * FROM testattempts WHERE TestID=$tid AND StudentID=$sid AND Attempt=$attempt";
		$result = $db->db_query($sql);
		$exist = $db->db_numrows($result);
		if($exist == 1) {
			return true;
		} else return false;
	}
	
	function getAllModuleWithoutUnit() {
		global $db;
		$sql = "SELECT m.* FROM modules m JOIN tests te ON m.ModuleID=te.ModuleID AND te.UnitID IS NULL 
				GROUP BY m.ModuleID ORDER BY m.ModuleID ASC";
		//echo $sql;exit;
		$result = $db->db_query($sql);
		if($result) {
			$rows = $db->db_fetchrowset($result);
			return $rows;
		} else return false;
	}
	
	function getAllDoneTestsInModule($sid, $mid) {
		global $db;
		$sql = "SELECT at.*, te.Title, m.ModuleName FROM testattempts at JOIN tests te ON at.TestID=te.TestID AND te.UnitID IS NULL 
				JOIN modules m ON te.ModuleID=m.ModuleID 
				WHERE at.StudentID=$sid AND m.ModuleID=$mid ORDER BY m.ModuleID ASC";
		//echo $sql;exit;
		$result = $db->db_query($sql);
		if($result) {
			$rows = $db->db_fetchrowset($result);
			return $rows;
		} else return false;
	}
	
	function showAllUnits() {
		global $db;
		$sql = "SELECT * FROM unit ORDER BY ModuleID, UnitID ASC";
		$result = $db->db_query($sql);
		if($result) {
			$rows = $db->db_fetchrowset($result);
			return $rows;
		} else return false;
	}
	
	function getDateTimeLimit($tid) {
		global $db;
		$sql = "SELECT * FROM tests WHERE TestID=$tid";
		$result = $db->db_query($sql);
		if($result) {
			$row = $db->db_fetchrow($result);
			return $row;
		} else return false;
	}
}

?>